package com.dongqb.dbhelper;

import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ParameterMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 */
public class SqliteHelper {

	private Connection connection;
	private Statement statement;
	private ResultSet resultSet;
	private String dbFilePath;

	public SqliteHelper(String dbFilePath) throws ClassNotFoundException, SQLException {
		this.dbFilePath = dbFilePath;
		connection = getConnection(dbFilePath);
	}

	public Connection getConnection(String dbFilePath) throws ClassNotFoundException, SQLException {
		Connection conn = null;
		Class.forName("org.sqlite.JDBC");
		conn = DriverManager.getConnection("jdbc:sqlite:" + dbFilePath);
		return conn;
	}

	public <T> T executeQuery(String sql, ResultSetExtractor<T> rse)
			throws SQLException, ClassNotFoundException, IOException {
		try {
			resultSet = getStatement().executeQuery(sql);
			T rs = rse.extractData(resultSet);
			return rs;
		} finally {
			destroyed();
		}
	}

	public <T> List<T> executeQuery(String sql, RowMapper<T> rm) throws SQLException, ClassNotFoundException {
		List<T> rsList = new ArrayList<T>();
		try {
			resultSet = getStatement().executeQuery(sql);
			while (resultSet.next()) {
				rsList.add(rm.mapRow(resultSet, resultSet.getRow()));
			}
		} finally {
			destroyed();
		}
		return rsList;
	}

	public int executeUpdate(String sql) throws SQLException, ClassNotFoundException {
		try {
			int c = getStatement().executeUpdate(sql);
			return c;
		} finally {
			destroyed();
		}
	}

	public int execute(String sql, Object... param) throws SQLException, ClassNotFoundException {
		try {
			PreparedStatement prep = getConnection().prepareStatement(sql);
			for (int i = 0; i < param.length; i++) {
				prep.setObject(i+1, param[i]);
			}
			int c = prep.executeUpdate();
			return c;
		} finally {
			destroyed();
		}
	}

	public void executeUpdate(String... sqls) throws SQLException, ClassNotFoundException {
		try {
			for (String sql : sqls) {
				getStatement().executeUpdate(sql);
			}
		} finally {
			destroyed();
		}
	}

	public void executeUpdate(List<String> sqls) throws SQLException, ClassNotFoundException {
		try {
			for (String sql : sqls) {
				getStatement().executeUpdate(sql);
			}
		} finally {
			destroyed();
		}
	}

	public void executeUpdate(String sql, List<Map<String, String>> list, String... col)
			throws SQLException, ClassNotFoundException {
		try {
			getConnection().setAutoCommit(false);
			PreparedStatement prep = getConnection().prepareStatement(sql);
			for (int i = 0; i < list.size(); i++) {
				for (int j = 0; j < col.length; j++) {
					prep.setString((j + 1), list.get(i).get(col[j]));
				}
				prep.addBatch();
			}
			prep.executeBatch();
			getConnection().setAutoCommit(true);
		} finally {
			destroyed();
		}
	}

	public void executeUpdate(String sql, List<String[]> list) throws SQLException, ClassNotFoundException {
		try {
			getConnection().setAutoCommit(false);
			PreparedStatement prep = getConnection().prepareStatement(sql);
			ParameterMetaData mata = prep.getParameterMetaData();
			for (int i = 0; i < list.size(); i++) {
				String[] data = list.get(i);
				int paramCount = mata.getParameterCount();
				for (int j = 0; j < paramCount; j++) {
					if (j >= data.length) {
						prep.setString((j + 1), "");
					} else {
						prep.setString((j + 1), data[j]);
					}
				}
				prep.addBatch();
			}
			prep.executeBatch();
			getConnection().setAutoCommit(true);
		} finally {
			destroyed();
		}
	}

	private Connection getConnection() throws ClassNotFoundException, SQLException {
		if (null == connection)
			connection = getConnection(dbFilePath);
		return connection;
	}

	private Statement getStatement() throws SQLException, ClassNotFoundException {
		if (null == statement)
			statement = getConnection().createStatement();
		return statement;
	}

	/**
	 * ��ݿ���Դ�رպ��ͷ�
	 */
	public void destroyed() {
		try {
			if (null != connection) {
				connection.close();
				connection = null;
			}

			if (null != statement) {
				statement.close();
				statement = null;
			}

			if (null != resultSet) {
				resultSet.close();
				resultSet = null;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}
}